﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DataAccess
{   
    public class data
    {
        #region varable
        /// <summary>
        /// 连接字符串
        /// </summary>
        public  static SqlConnection conn = null;
        static SqlCommand cmd = null;
        static SqlDataReader sdr = null;
        static SqlDataAdapter sda = null;
        public static string ServerName = null;
        public static string ID = null;
        public static string passWord = null;
        public static string database = null;
        #endregion

        static data()
        {
        }
        /// <summary>
        /// 初始化数据库连接
        /// </summary>
        public static string InitDataBase(string serverName, string dbName, string id, string pwd)
        {
            ServerName = serverName;
            ID = id;
            passWord = pwd;
            database = dbName;
            if (ServerName == "" || ServerName == null)
            {
                return "。。Server name is not exist!";
            }
            try
            {
                conn = new SqlConnection(@"Data Source=" + ServerName + ";Initial Catalog="+ database + ";Persist Security Info=True;User ID=" + ID + ";Password=" + passWord);
                cmd = new SqlCommand();
                cmd.Connection = conn;
                conn.Open();
                sda = new SqlDataAdapter();
                conn.Close();
            }
            catch (System.Exception ex)
            {
                return ex.Message;
            }
            return "数据库连接成功";
        }
        /// <summary>
        /// 给存储过程传值
        /// </summary>
        /// <param name="sqlstring">SQL存储过程</param>
        /// <param name="cmdtype">SQL命令类型</param>
        /// <param name="paranames">存储过程参数</param>
        /// <param name="paravalues">返回值</param>
        /// <returns></returns>
        private static SqlCommand preparecommand(string sqlstring, CommandType cmdtype, string[] paranames, object[] paravalues)
        {
            cmd.CommandText = sqlstring;
            cmd.CommandType = cmdtype;
            cmd.Parameters.Clear();
            if (paranames != null)
            {
                int count = paranames.Length;
                for (int i = 0; i < count; i++)
                {
                    cmd.Parameters.AddWithValue(paranames[i], paravalues[i]);
                }
            }
            return cmd;
        }

        /// <summary>
        /// 获取数据集
        /// </summary>
        /// <param name="sqlText">SQL存储过程</param>
        /// <param name="tableName">数据表名</param>
        /// <param name="commandType">SQL命令类型</param>
        /// <returns></returns>
        public static DataSet GetDataSet(string sqlText, int tableName, CommandType commandType)
        {
            cmd = preparecommand(sqlText, commandType, null, null);
            DataSet ds = new DataSet();
            sda.SelectCommand = cmd;
            sda.Fill(ds);
            return ds;
        }

        /// <summary>
        /// 执行带有返回值,有输出参数的存储过程
        /// </summary>
        /// <param name="sqltext">sql存储过程</param>
        /// <param name="commandtype">sql命令类型</param>
        /// <param name="paras">参数</param>
        /// <param name="values">参数值</param>
        /// <param name="para">参数</param>
        /// <param name="value">参数值</param>
        /// <param name="type">命令类型</param>
        /// <returns></returns>
        public static int executesql(string sqltext, CommandType commandtype, string[] paras, object[] values, string para, out object value, SqlDbType type)
        {
            cmd = preparecommand(sqltext, commandtype, paras, values);
            if (para != null)
            {
                SqlParameter pram = new SqlParameter(para, type);
                cmd.Parameters.Add(pram);
                cmd.Parameters[para].Direction = ParameterDirection.Output;
            }
            SqlParameter parReturn = new SqlParameter("@return", SqlDbType.Int);
            parReturn.Direction = ParameterDirection.ReturnValue; 　　//参数类型为ReturnValue                    
            cmd.Parameters.Add(parReturn);
            if (conn.State != ConnectionState.Open)
                conn.Open();
            int count;
            count = cmd.ExecuteNonQuery();
            if (para != null)
                value = cmd.Parameters[para].Value;
            else
                value = null;
            conn.Close();
            return (int)parReturn.Value;
        }
        /// <summary>
        /// 执行有返回值存储过程
        /// </summary>
        /// <param name="sqltext">sql存储过程名</param>
        /// <param name="commandtype">命令类型</param>
        /// <param name="paras">参数</param>
        /// <param name="values">参数值</param>
        /// <returns></returns>
        public static int executesql(string sqltext, CommandType commandtype, string[] paras, object[] values)
        {
            int count;
            object outvalue;
            string para = null;
            count = executesql(sqltext, commandtype, paras, values, para, out outvalue, SqlDbType.Int);
            return count;
        }

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="sqlString"></param>
        /// <param name="cmdType"></param>
        /// <param name="paras"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static object GetValues(string sqlString, CommandType cmdType, string[] paras, object[] values)
        {
            string val = string.Empty;
            if (paras.Length != values.Length)
                throw new Exception("请输入正确的参数");
            object[] value = null;
            cmd = preparecommand(sqlString, cmdType, paras, values);
            try
            {
                conn.Open();
                sdr = cmd.ExecuteReader();
                int count = sdr.FieldCount;
                if (sdr.Read())
                {
                    value = new object[count];
                    for (int i = 0; i < count; i++)
                    {
                        value[i] = sdr.GetValue(i);
                        val += value[i];
                    }
                }
                sdr.Close();
                conn.Close();
            }
            finally
            {
                conn.Close();
            }
            return val;
        }
        /// <summary>
        /// 获取数量
        /// </summary>
        /// <param name="sqlString"></param>
        /// <param name="cmdType"></param>
        /// <param name="paras"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static int GetINTValues(string sqlString, CommandType cmdType, string[] paras, object[] values)
        {
            int val = 0;
            if (paras.Length != values.Length)
                throw new Exception("请输入正确的参数");
            int[] value = null;
            cmd = preparecommand(sqlString, cmdType, paras, values);
            try
            {
                conn.Open();
                sdr = cmd.ExecuteReader();
                int count = sdr.FieldCount;
                if (sdr.Read())
                {
                    value = new int[count];
                    for (int i = 0; i < count; i++)
                    {
                        if (!(Convert.IsDBNull(sdr.GetValue(i))))
                        {
                            value[i] = Convert.ToInt32(sdr.GetValue(i));
                            val += value[i];
                        }
                    }
                }
                sdr.Close();
                conn.Close();
            }
            finally
            {
                conn.Close();
            }
            return val;
        }
        /// <summary>
        /// 执行一个SQL 命令，返回数据所组成的数组
        /// </summary>
        public static object[] GetValues(string commandText, CommandType commandType, string[] paras, object[] values, SqlDbType type)
        {

            if (paras.Length != values.Length)
                throw new Exception("请输入正确的参数");
            object[] returnValues = null;
            cmd = preparecommand(commandText, commandType, paras, values);
            cmd.Connection.Open();
            //SqlParameter pram = new SqlParameter(para, type);
            //cmd.Parameters.Add(pram);
            //cmd.Parameters[para].Direction = ParameterDirection.Output;
            sdr = cmd.ExecuteReader();

            if (sdr.Read())
            {
                int count = sdr.FieldCount;
                returnValues = new object[count];
                for (int i = 0; i < count; i++)
                {
                    returnValues[i] = sdr.GetValue(i);
                }
            }

            sdr.Close();
            cmd.Connection.Close();
            //value = cmd.Parameters[para].Value;
            return returnValues;
        }
        public static object[] GetValues(string sqlString, CommandType cmdType)
        {
            object[] value = null;
            cmd = preparecommand(sqlString, cmdType, null, null);
            conn.Open();
            sdr = cmd.ExecuteReader();
            int count = sdr.FieldCount;
            if (sdr.Read())
            {
                value = new object[count];
                for (int i = 0; i < count; i++)
                {
                    value[i] = sdr.GetValue(i);
                }
            }
            sdr.Close();
            conn.Close();
            return value;

        }
        //public static object[] GetValues(string sqlString, CommandType cmdType, string[] paras, object[] values)
        //{
        //    if (paras.Length != values.Length)
        //        throw new Exception("请输入正确的参数");
        //    object[] value = null;
        //    cmd = preparecommand(sqlString, cmdType, paras, values);
        //    conn.Open();
        //    sdr = cmd.ExecuteReader();
        //    int count = sdr.FieldCount;
        //    if (sdr.Read())
        //    {
        //        value = new object[count];
        //        for (int i = 0; i < count; i++)
        //        {
        //            value[i] = sdr.GetValue(i);
        //        }
        //    }
        //    sdr.Close();
        //    conn.Close();
        //    return value;
        //}
        /// <summary>
        /// 带参数返回数据集
        /// </summary>
        public static DataSet GetDataSet(string sqlText, int tableName, CommandType commandType, string[] paras, object[] values)
        {
            if (paras.Length != values.Length)
                throw new Exception("请输入正确的参数和参数名");

            cmd = preparecommand(sqlText, commandType, paras, values);

            DataSet ds = new DataSet();
            sda.SelectCommand = cmd;
            sda.Fill(ds);
            conn.Close();
            return ds;
        }
        /// <summary>
        /// 无参数返回数据集
        /// </summary>
        public static DataSet GetDataSet(string sqlText, string tableName, CommandType commandType)
        {
            cmd = preparecommand(sqlText, commandType, null, null);
            DataSet ds = new DataSet();
            sda.SelectCommand = cmd;
            sda.Fill(ds, tableName);
            return ds;
        }
    }
}
